﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using UygulamaTakbisService.wsTakbisServis;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.Diagnostics;

namespace UygulamaTakbisService
{
    public class TuzelKisiBilgiDao : IDisposable
    {
        TAKBISWebService takbisServis = new TAKBISWebService();

        public TuzelKisiBilgi GetirTuzelKisiBilgiIDDen(decimal tuzelKisiID, long kullaniciID)
        {
            TuzelKisiBilgi tuzelKisiBilgi = null;
            try
            {
                using (LogDao logDao = new LogDao())
                {
                    Stopwatch stopwatch = new Stopwatch();
                    stopwatch.Start();
                    tuzelKisiBilgi = takbisServis.GetirTuzelKisiBilgiIDDen(tuzelKisiID);
                    stopwatch.Stop();

                    Stopwatch stopwatch2 = new Stopwatch();
                    if (tuzelKisiBilgi != null)
                    {
                        stopwatch2.Start();
                        bool tuzelKisiBilgiKayitEdildiMi = TuzelKisiBilgiKaydet(tuzelKisiBilgi);
                        if (tuzelKisiBilgiKayitEdildiMi)
                        {
                            if (tuzelKisiBilgi.TuzelKisiTip != null && !TuzelKisiTipBilgiKaydet(tuzelKisiBilgi.TuzelKisiTip))
                            {
                                bool tuzelKisiTipKayitEdildiMi = TuzelKisiTipBilgiKaydet(tuzelKisiBilgi.TuzelKisiTip);
                            }
                        }
                        stopwatch2.Stop();
                    }
                    else
                        tuzelKisiBilgi = null;
                    logDao.TakbisBilgiSorguLogKaydet("GetirTuzelKisiBilgiIDDen", null, tuzelKisiBilgi, "TüzelKişiID = " + tuzelKisiID.ToString(), Convert.ToDecimal(stopwatch.ElapsedMilliseconds), Convert.ToDecimal(stopwatch2.ElapsedMilliseconds), kullaniciID);
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return tuzelKisiBilgi;
        }

        public bool TuzelKisiBilgiKaydet(TuzelKisiBilgi tuzelKisiBilgi)
        {
            bool sonuc;
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguLogConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"INSERT INTO TuzelKisiBilgi with(updlock) (TuzelKisiBilgiID,Ad,TuzelKisiTipBilgiID,VergiNo,SicilNo,YabanciTemsilciMi,YabanciSermayeliMi,UlkeBilgiID,AktifMi,KayitTarih) VALUES (@TuzelKisiBilgiID,@Ad,@TuzelKisiTipBilgiID,@VergiNo,@SicilNo,@YabanciTemsilciMi,@YabanciSermayeliMi,@UlkeBilgiID,@AktifMi,getdate())";

                        if (tuzelKisiBilgi.TuzelKisiID != null)
                            cmd.Parameters.Add("@TuzelKisiBilgiID", tuzelKisiBilgi.TuzelKisiID);
                        else
                            cmd.Parameters.Add("@TuzelKisiBilgiID", DBNull.Value);

                        if (tuzelKisiBilgi.Ad != null)
                            cmd.Parameters.Add("@Ad", tuzelKisiBilgi.Ad);
                        else
                            cmd.Parameters.Add("@Ad", DBNull.Value);

                        if (tuzelKisiBilgi.TuzelKisiTip.TuzelKisiTipBilgiID != null)
                            cmd.Parameters.Add("@TuzelKisiTipBilgiID", tuzelKisiBilgi.TuzelKisiTip.TuzelKisiTipBilgiID);
                        else
                            cmd.Parameters.Add("@TuzelKisiTipBilgiID", DBNull.Value);

                        if (tuzelKisiBilgi.VergiNo != null)
                            cmd.Parameters.Add("@VergiNo", tuzelKisiBilgi.VergiNo);
                        else
                            cmd.Parameters.Add("@VergiNo", DBNull.Value);

                        if (tuzelKisiBilgi.SicilNo != null)
                            cmd.Parameters.Add("@SicilNo", tuzelKisiBilgi.SicilNo);
                        else
                            cmd.Parameters.Add("@SicilNo", DBNull.Value);

                        if (tuzelKisiBilgi.YabanciTemsilciMi != null)
                            cmd.Parameters.Add("@YabanciTemsilciMi", (tuzelKisiBilgi.YabanciTemsilciMi == true ? "1" : "2"));
                        else
                            cmd.Parameters.Add("@YabanciTemsilciMi", DBNull.Value);

                        if (tuzelKisiBilgi.YabanciSermayeliMi != null)
                            cmd.Parameters.Add("@YabanciSermayeliMi", (tuzelKisiBilgi.YabanciSermayeliMi == true ? "1" : "2"));
                        else
                            cmd.Parameters.Add("@YabanciSermayeliMi", DBNull.Value);

                        if (tuzelKisiBilgi.Ulke != null)
                            cmd.Parameters.Add("@UlkeBilgiID", tuzelKisiBilgi.Ulke.UlkeBilgiID);
                        else
                            cmd.Parameters.Add("@UlkeBilgiID", DBNull.Value);

                        if (tuzelKisiBilgi.AktifMi != null)
                            cmd.Parameters.Add("@AktifMi", (byte)tuzelKisiBilgi.AktifMi);
                        else
                            cmd.Parameters.Add("@AktifMi", DBNull.Value);

                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        cmd.ExecuteScalar();
                        transaction.Commit();
                        sonuc = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        sonuc = false;
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return sonuc;
                }
            }
        }

        public bool TuzelKisiBilgiKayitliMi(decimal tuzelKisiBilgiID)
        {
            bool sonuc = false;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguLogConnection"].ToString()))
                {
                    string sql = string.Format(@"SELECT * FROM TuzelKisiBilgi with(nolock) WHERE TuzelKisiBilgiID = {0} ", tuzelKisiBilgiID);
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count >= 1)
                                sonuc = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return sonuc;
        }

        public bool TuzelKisiTipBilgiKaydet(TuzelKisiTipBilgi tuzelKisiTipBilgi)
        {
            bool sonuc;
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguLogConnection"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    con.Open();
                    SqlTransaction transaction = con.BeginTransaction("trans");
                    try
                    {
                        cmd.CommandText = @"INSERT INTO TuzelKisiTipBilgi with(updlock) (TuzelKisiTipBilgiID,Ad,KayitTarih) VALUES (@TuzelKisiTipBilgiID,@Ad,GETDATE())";

                        if (tuzelKisiTipBilgi.TuzelKisiTipBilgiID != null)
                            cmd.Parameters.Add("@TuzelKisiTipBilgiID", tuzelKisiTipBilgi.TuzelKisiTipBilgiID);
                        else
                            cmd.Parameters.Add("@TuzelKisiTipBilgiID", DBNull.Value);

                        if (tuzelKisiTipBilgi.Ad != null)
                            cmd.Parameters.Add("@Ad", tuzelKisiTipBilgi.Ad);
                        else
                            cmd.Parameters.Add("@Ad", DBNull.Value);

                        cmd.Connection = con;
                        cmd.Transaction = transaction;
                        cmd.ExecuteScalar();
                        transaction.Commit();
                        sonuc = true;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        sonuc = false;
                        using (ExceptionLogDao el = new ExceptionLogDao())
                        {
                            el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                        }
                    }
                    finally
                    {
                        con.Close();
                        con.Dispose();
                        cmd.Dispose();
                    }
                    return sonuc;
                }
            }
        }

        public bool TuzelKisiTipBilgiKayitliMi(TuzelKisiTipBilgi tuzelKisiTipBilgi)
        {
            bool sonuc = false;
            try
            {
                using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TakbisBilgiSorguLogConnection"].ToString()))
                {
                    string sql = string.Format(@"SELECT * 
                                                 FROM TuzelKisiTipBilgi with(nolock) 
                                                 WHERE TuzelKisiTipBilgiID = {0} and
                                                       Ad like '{1}' ", tuzelKisiTipBilgi.TuzelKisiTipBilgiID, tuzelKisiTipBilgi.Ad);
                    using (SqlCommand cmd = new SqlCommand(sql))
                    {
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            cmd.Connection = con;
                            sda.SelectCommand = cmd;
                            cmd.CommandTimeout = 60000;
                            DataTable dt = new DataTable();
                            sda.Fill(dt);
                            if (dt.Rows.Count >= 1)
                                sonuc = true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return sonuc;
        }

        public List<TuzelKisiTipBilgi> GetirTuzelKisiTipBilgiTum(long kullaniciID)
        {
            List<TuzelKisiTipBilgi> tuzelKisiTipBilgiList = null;
            try
            {
                using (LogDao logDao = new LogDao())
                {
                    Stopwatch stopwatch = new Stopwatch();
                    stopwatch.Start();
                    TuzelKisiTipBilgi[] tuzelKisiTipBilgiArray = takbisServis.GetirTuzelKisiTipBilgiTum();
                    if (tuzelKisiTipBilgiArray != null)
                        tuzelKisiTipBilgiList = tuzelKisiTipBilgiArray.ToList();
                    else
                        tuzelKisiTipBilgiList = null;
                    stopwatch.Stop();

                    Stopwatch stopwatch2 = new Stopwatch();
                    if (tuzelKisiTipBilgiList != null)
                    {
                        stopwatch2.Start();
                        foreach (TuzelKisiTipBilgi tuzelKisiTipBilgi in tuzelKisiTipBilgiList)
                        {
                            if (!TuzelKisiTipBilgiKayitliMi(tuzelKisiTipBilgi))
                            {
                                bool tuzelKisiTipBilgiKayitEdildiMi = TuzelKisiTipBilgiKaydet(tuzelKisiTipBilgi);
                            }
                        }
                        stopwatch2.Stop();
                    }
                    else
                        tuzelKisiTipBilgiList = null;
                    logDao.TakbisBilgiSorguLogKaydet("GetirTuzelKisiTipBilgiTum", null, tuzelKisiTipBilgiList, null, Convert.ToDecimal(stopwatch.ElapsedMilliseconds), Convert.ToDecimal(stopwatch2.ElapsedMilliseconds), kullaniciID);
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return tuzelKisiTipBilgiList;
        }

        public List<TuzelKisiBilgi> GetirTuzelKisiBilgi(SorguTip sorguTip, string tuzelKisiAd, string vergiNo, string sicilNo, TapuBolumDurum tapuBolumDurum, BolgeTip bolgeTip, decimal bolgeID, TasinmazTip tasinmazTip, long kullaniciID)
        {
            List<TuzelKisiBilgi> tuzelKisiBilgiList = null;
            try
            {
                using (LogDao logDao = new LogDao())
                {
                    Stopwatch stopwatch = new Stopwatch();
                    stopwatch.Start();
                    tuzelKisiBilgiList = takbisServis.GetirTuzelKisiBilgi(sorguTip, tuzelKisiAd, vergiNo, sicilNo, tapuBolumDurum, bolgeTip, bolgeID, tasinmazTip).ToList();
                    stopwatch.Stop();

                    Stopwatch stopwatch2 = new Stopwatch();
                    if (tuzelKisiBilgiList != null && tuzelKisiBilgiList.Count != 0)
                    {
                        stopwatch2.Start();
                        foreach (TuzelKisiBilgi tuzelKisiBilgi in tuzelKisiBilgiList)
                        {
                            bool tuzelKisiBilgiKayitEdildiMi = TuzelKisiBilgiKaydet(tuzelKisiBilgi);
                            if (tuzelKisiBilgiKayitEdildiMi)
                            {
                                if (tuzelKisiBilgi.TuzelKisiTip != null && !TuzelKisiTipBilgiKaydet(tuzelKisiBilgi.TuzelKisiTip))
                                {
                                    bool tuzelKisiTipKayitEdildiMi = TuzelKisiTipBilgiKaydet(tuzelKisiBilgi.TuzelKisiTip);
                                }
                            }
                        }
                        stopwatch2.Stop();
                    }
                    else
                        tuzelKisiBilgiList = null;
                    logDao.TakbisBilgiSorguLogKaydet("GetirTuzelKisiBilgi", null, tuzelKisiBilgiList, "SorguTip = " + sorguTip.ToString() + ", TüzelKişiAd = " + tuzelKisiAd + ", VergiNo = " + vergiNo + ", SicilNo = " + sicilNo + ", TapuBolumDurum = " + tapuBolumDurum.ToString() + " BölgeTip" + bolgeTip.ToString() + " BölgeID = " + bolgeID, Convert.ToDecimal(stopwatch.ElapsedMilliseconds), Convert.ToDecimal(stopwatch2.ElapsedMilliseconds), kullaniciID);
                }
            }
            catch (Exception ex)
            {
                using (ExceptionLogDao el = new ExceptionLogDao())
                {
                    el.HataLogYaz(DateTime.Now.ToString() + "  " + ex.ToString());
                }
            }
            return tuzelKisiBilgiList;
        }

        public void Dispose()
        {
        }
    }
}
